import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class Marketing {

	// táblák:
	// notification (id, type, html_message, text_message)
	// marketing (user_id, notif_id, send_date)

	enum NotifType {
		NOT_INVITERS_AFTER_ONE_WEEK;
	}
	
	enum TargetType {
		EMAIL, FB_NOTIFICATION;
	}
	
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		try {
			new Marketing().processNotInvitersAfterOneWeek();

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	private Notification getLastNotificationByType(String notifType, TargetType targetType) throws Exception {
		Notification notification = null;

		Connection connection = Scanner.getConnection();
		connection.setAutoCommit(false);

		String querySql = "SELECT * from notification_type nt1 where nt1.notif_id = (SELECT MAX(notif_id) from notification_type where notif_type = '" + notifType + "' and target_type = " + targetType.ordinal()  + ")";
		ResultSet rs = connection.createStatement().executeQuery(querySql);
		
		if (rs.next()) {
			notification = new Notification();
			notification.setId(rs.getInt("notif_id"));
			notification.setSubject("subject");
			notification.setHtmlMessage(rs.getString("html_message"));
			notification.setTextMessage(rs.getString("text_message"));
		}

		return notification;
	}
	
	private String createTrackId() {
		return "" + System.currentTimeMillis() + ((int)(Math.random() * 10000));
	}

	private void processNotInvitersAfterOneWeek() throws Exception {
		Connection connection = Scanner.getConnection();
		connection.setAutoCommit(true);

		PreparedStatement insertMarketing = connection.prepareStatement("insert into marketing set notfication_id = ?, send_date = NOW(), customer_id = ?, track_id = ?");
		
		Notification notif = getLastNotificationByType(NotifType.NOT_INVITERS_AFTER_ONE_WEEK.name(), TargetType.EMAIL);
				
		String querySql = "SELECT oc_customer.customer_id, oc_customer.firstname, oc_customer.lastname, oc_customer.email FROM oc_customer WHERE customer_id not in (SELECT oc_fb_send_out.customer_id FROM oc_fb_send_out) AND customer_id NOT IN " +
				"(select marketing.customer_id " +
				"from marketing, notification_type " +
				"where marketing.notfication_id = notification_type.notif_id and notification_type.notif_type = '" + NotifType.NOT_INVITERS_AFTER_ONE_WEEK.name() + "' " +
						"and notification_type.target_type = " + TargetType.EMAIL.ordinal() + ")";
		ResultSet rs = connection.createStatement().executeQuery(querySql);
		
		while (rs.next()) {
			String firstname = rs.getString("firstname");
			String lastname = rs.getString("lastname");
			String email = rs.getString("email");

			String name = email;
			if (firstname != null && !firstname.trim().isEmpty()) {
				name = firstname;

			} else if (lastname != null && !lastname.trim().isEmpty()) {
				name = lastname;
			}
			
			//gereate trackId
			String trackId = createTrackId();

			if (email != null && !email.trim().isEmpty()) {
				SendMail2.createMail(email, name, trackId, notif);

				// store
				insertMarketing.setInt(1, notif.getId());
				insertMarketing.setInt(2, rs.getInt("customer_id"));
				insertMarketing.setString(3, trackId);
				insertMarketing.executeUpdate();
			}
			
			break;
		}		

	}

}
